﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CloudApiServer.Services
{
    /// <summary>
    /// 目录上传/撤销/查询服务
    /// </summary>
    public class CatalogQueryService
    {
        /// <summary>
        /// 3301 目录上传对照
        /// </summary>
        /// <param name="fsiRequest">入参</param>
        /// <param name="ControllerName">来源控制器名称</param>
        /// <returns>出参</returns>
        /// <exception cref="Exception"></exception>
        public async Task<bool> CatalogCompUp(FsiRequestHeader<InputCatalogCompUp> fsiRequest, string ControllerName)
        {
            try
            {
                string fsiadmvs = fsiRequest.mdtrtarea_admvs.Substring(0, 2);
                List<DataCatalogCompUp> list = new List<DataCatalogCompUp>();
                list = fsiRequest.Input.data;
                string fixmedins_code = fsiRequest.fixmedins_code;
                if (string.IsNullOrEmpty(fixmedins_code) || list.Count == 0)
                {
                    throw new Exception("参数校验失败：传入的机构代码|上传商品数组为空，请检查入参！");
                }

                string connectionString =
                    $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
                string error = null;
                using (var conn = new MySqlConnection(connectionString))
                {
                    await conn.OpenAsync();
                    using (var transaction = conn.BeginTransaction())
                    {
                        foreach (var item in list)
                        {
                            try
                            {
                                string query = "INSERT INTO fsi_catalogcompup  VALUES (@fixmedins_code, @fixmedins_hilist_id, @fixmedins_hilist_name,@list_type, @med_list_codg, @begndate, @enddate, @aprvno, @dosform, @exct_cont, @item_cont, @prcunt, @spec, @pacspec, @memo, @scdz, @prdr_name, now())";
                                var parameters = new List<MySqlParameter>
                                {
                                    new MySqlParameter("@fixmedins_code", fixmedins_code),
                                    new MySqlParameter("@fixmedins_hilist_id", item.fixmedins_hilist_id),
                                    new MySqlParameter("@fixmedins_hilist_name", item.fixmedins_hilist_name),
                                    new MySqlParameter("@list_type", item.list_type),
                                    new MySqlParameter("@med_list_codg", item.med_list_codg),
                                    new MySqlParameter("@begndate", item.begndate),
                                    new MySqlParameter("@enddate", item.enddate),
                                    new MySqlParameter("@aprvno", item.aprvno),
                                    new MySqlParameter("@dosform", item.dosform),
                                    new MySqlParameter("@exct_cont", item.exct_cont),
                                    new MySqlParameter("@item_cont", item.item_cont),
                                    new MySqlParameter("@prcunt", item.prcunt),
                                    new MySqlParameter("@spec", item.spec),
                                    new MySqlParameter("@memo", item.memo),
                                    new MySqlParameter("@scdz", item.scdz),
                                    new MySqlParameter("@prdr_name", item.prdr_name)
                                };
                                await MySqlHelper.ExecuteNonQueryAsync(connectionString, query, parameters, transaction);
                            }
                            catch (Exception ex)
                            {
                                error += $"【{item.fixmedins_hilist_name}/{item.spec}/{item.prcunt}/{item.prdr_name}/{item.aprvno}】上传出错：{ex.Message};";
                            }
                        }

                        if (!string.IsNullOrEmpty(error))
                        {
                            transaction.Rollback();
                            throw new Exception(error);
                        }

                        transaction.Commit();
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(
                    $"[CatalogQueryService.CatalogCompUp]目录上传对照异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
                throw;
            }
        }
        /// <summary>
        /// 3302 目录上传撤销
        /// </summary>
        /// <param name="fsiRequest">入参</param>
        /// <param name="ControllerName">来源控制器名称</param>
        /// <returns>出参</returns>
        /// <exception cref="Exception"></exception>
        public async Task<bool> DeleteCatalogCompAudited(FsiRequestHeader<InputDeleteCatalogCompAudited> fsiRequest, string ControllerName)
        {
            try
            {
                string fsiadmvs = fsiRequest.mdtrtarea_admvs.Substring(0, 2);
                string fixmedins_code = fsiRequest.Input.data.fixmedins_code;
                string fixmedins_hilist_id = fsiRequest.Input.data.fixmedins_hilist_id;
                string med_list_codg = fsiRequest.Input.data.med_list_codg;

                if (string.IsNullOrEmpty(fixmedins_code) || string.IsNullOrEmpty(fixmedins_hilist_id) || string.IsNullOrEmpty(med_list_codg))
                {
                    throw new Exception("参数校验失败：传入的机构代码|医疗机构内部编码|医保国家编码为空，请检查入参！");
                }

                string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
                string query = "select * from fsi_catalogcompup where fixmedins_code=@fixmedins_code and fixmedins_hilist_id=@fixmedins_hilist_id ";
                var parameter = new List<MySqlParameter>
                {
                    new MySqlParameter("@fixmedins_code", fixmedins_code),
                    new MySqlParameter("@fixmedins_hilist_id", fixmedins_hilist_id),
                };
                DataTable dt = await MySqlHelper.ExecuteQueryAsync(connectionString, query, parameter);
                if (dt.Rows.Count < 1)
                {
                    throw new Exception(
                        $"查询商品上传记录表失败：根据传入的机构代码【{fixmedins_code}】、医疗机构内部编码【{fixmedins_hilist_id}】未找商品已上传记录！");
                }
                query = "delete from fsi_catalogcompup where fixmedins_code=@fixmedins_code and fixmedins_hilist_id=@fixmedins_hilist_id ";
                await MySqlHelper.ExecuteNonQueryAsync(connectionString, query, parameter);
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.Error(
                    $"[CatalogQueryService.DeleteCatalogCompAudited]目录上传撤销异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
                throw;
            }
        }
        
        /// <summary>
        /// 1316 医疗与医保目录匹配查询
        /// </summary>
        /// <param name="fsiRequest">入参</param>
        /// <param name="ControllerName">来源控制器名称</param>
        /// <returns>出参</returns>
        /// <exception cref="Exception"></exception>
        public async Task<OutputQueryMedinsHilistMapByPage> QueryMedinsHilistMapByPage(FsiRequestHeader<InputQueryMedinsHilistMapByPage> fsiRequest, string ControllerName)
        {
            try
            {
                string fsiadmvs = fsiRequest.mdtrtarea_admvs.Substring(0, 2);
                
                string query_date = fsiRequest.Input.data.query_date;//查询时间点
                
                string med_list_codg = fsiRequest.Input.data.med_list_codg; //医疗目录编码
                string hilist_code = fsiRequest.Input.data.hilist_code;//医保目录编码
                string list_type = fsiRequest.Input.data.list_type;//目录类别
                string insu_admdvs = fsiRequest.Input.data.insu_admdvs;//参保机构医保区划
                string vali_flag = fsiRequest.Input.data.vali_flag;//有效标志

                string begndate = fsiRequest.Input.data.begndate;//开始日期
                string updt_time = fsiRequest.Input.data.updt_time;//结束时间
                
                int page_num = fsiRequest.Input.data.page_num; //当前页
                int page_size = fsiRequest.Input.data.page_size; //本页数据

                if (string.IsNullOrEmpty(updt_time) || page_num == 0 || page_size == 0)
                {
                    throw new Exception("参数校验失败：传入的结束时间|当前页码|当前页条数为空，请检查入参！");
                }

                string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
                


                int now_page = (page_num - 1) * page_size;
                Random rnd = new Random();
                DateTime dateTime = DateTime.Now;
                string query = "select  a.MED_LIST_CODG,a.HILIST_CODE,a.LIST_TYPE,a.INSU_ADMDVS,a.BEGNDATE,a.ENDDATE,a.MEMO,a.VALI_FLAG,a.RID,a.UPDT_TIME,a.CRTER_ID,a.CRTER_NAME,a.CRTE_TIME,a.CRTE_OPTINS_NO,a.OPTER_ID,a.OPTER_NAME,a.OPT_TIME,a.OPTINS_NO,a.POOLAREA_NO from ml_ylybmlpp a where 1=1  ";
                string countQuery = "select count(*)  from ml_ylybmlpp a where 1=1 ";


                if (!string.IsNullOrEmpty(med_list_codg))
                {
                    query += " and MED_LIST_CODG=@med_list_codg ";
                    countQuery += " and MED_LIST_CODG=@med_list_codg ";
                }
                
                if (!string.IsNullOrEmpty(hilist_code))
                {
                    query += " and HILIST_CODE=@hilist_code ";
                    countQuery += " and HILIST_CODE=@hilist_code ";
                }

                if (!string.IsNullOrEmpty(list_type))
                {
                    query += " and LIST_TYPE=@list_type ";
                    countQuery += " and LIST_TYPE=@list_type ";
                }
                
                if (!string.IsNullOrEmpty(insu_admdvs))
                {
                    query += " and INSU_ADMDVS=@insu_admdvs ";
                    countQuery += " and INSU_ADMDVS=@insu_admdvs ";
                }
                
                if (!string.IsNullOrEmpty(vali_flag))
                {
                    query += " and VALI_FLAG=@vali_flag ";
                    countQuery += " and VALI_FLAG=@vali_flag ";
                }
                
                if (!string.IsNullOrEmpty(begndate))
                {
                    query += " and BEGNDATE >= @begndate ";
                    countQuery += " and BEGNDATE >= @begndate ";
                }
                
                if (!string.IsNullOrEmpty(updt_time))
                {
                    query += " and UPDT_TIME >= @updt_time ";
                    countQuery += " and UPDT_TIME >= @updt_time ";
                }
                query += " limit @nowpage,@pagesize";
                
                var parameter = new List<MySqlParameter>
                {
                    new MySqlParameter("@med_list_codg", med_list_codg),
                    new MySqlParameter("@hilist_code", hilist_code),
                    new MySqlParameter("@list_type", list_type ),
                    new MySqlParameter("@vali_flag", vali_flag),
                    new MySqlParameter("@insu_admdvs", insu_admdvs),
                    new MySqlParameter("@begndate", begndate),
                    new MySqlParameter("@updt_time", updt_time),
                    
                    new MySqlParameter("@nowpage", now_page),
                    new MySqlParameter("@pagesize", page_size),
                };
                
                DataTable dt = await MySqlHelper.ExecuteQueryAsync(connectionString, query, parameter);
                int count = await MySqlHelper.ExecuteScalarAsync<int>(connectionString, countQuery, parameter);
                
                List<OutputDataQueryMedinsHilistMapByPage> dataList = new List<OutputDataQueryMedinsHilistMapByPage>();
                foreach (DataRow row in dt.Rows)
                {
                    OutputDataQueryMedinsHilistMapByPage item = new OutputDataQueryMedinsHilistMapByPage
                    {
                        med_list_codg = row["MED_LIST_CODG"].ToString(),
                        hilist_code = row["HILIST_CODE"].ToString(),
                        list_type = row["LIST_TYPE"].ToString(),
                        insu_admdvs = row["INSU_ADMDVS"].ToString(),
                        begndate = row["BEGNDATE"].ToString(),
                        enddate = row["ENDDATE"].ToString(),
                        memo = row["MEMO"].ToString(),
                        vali_flag = row["VALI_FLAG"].ToString(),
                        rid = row["RID"].ToString(),
                        updt_time = row["UPDT_TIME"].ToString(),
                        crter_id = row["CRTER_ID"].ToString(),
                        crter_name = row["CRTER_NAME"].ToString(),
                        crte_time = row["CRTE_TIME"].ToString(),
                        crte_optins_no = row["CRTE_OPTINS_NO"].ToString(),
                        opter_id = row["OPTER_ID"].ToString(),
                        opter_name = row["OPTER_NAME"].ToString(),
                        opt_time = row["OPT_TIME"].ToString(),
                        optins_no = row["OPTINS_NO"].ToString(),
                        poolarea_no = row["POOLAREA_NO"].ToString(),
                    };
                    dataList.Add(item);
                }

                var pageCount = (int)Math.Ceiling((double)count / (double)page_size);

                var output = new OutputQueryMedinsHilistMapByPage
                {
                    recordCounts = count,
                    pages = pageCount,
                    data = dataList,
                    size = page_size,
                    firstPage = page_num == 1,
                    lastPage = page_num >= pageCount,
                    startRow = 1,
                    endRow = dt.Rows.Count,
                    pageSize = page_size,
                    pageNum = page_num
                };
                return output;
            }
            catch (Exception ex)
            {
                LogHelper.Error(
                    $"[CatalogQueryService.QueryMedinsHilistMapByPage]医疗与医保目录匹配数据获取异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
                throw;
            }
        }
        
        
        /// <summary>
        /// 1317 查询定点医药机构目录上传记录
        /// </summary>
        /// <param name="fsiRequest">入参</param>
        /// <param name="ControllerName">来源控制器名称</param>
        /// <returns>出参</returns>
        /// <exception cref="Exception"></exception>
        public async Task<OutputQueryMedListMapByPage> QueryMedListMapByPage(FsiRequestHeader<InputQueryMedListMapByPageRequest> fsiRequest, string ControllerName)
        {
            try
            {
                string fsiadmvs = fsiRequest.mdtrtarea_admvs.Substring(0, 2);
                string fixmedins_code = fsiRequest.Input.data.fixmedins_code;
                string medins_list_codg = fsiRequest.Input.data.medins_list_codg; //定点医药机构目录编号
                string medins_list_name = fsiRequest.Input.data.medins_list_name; //定点医药机构目录名称
                string med_list_codg = fsiRequest.Input.data.med_list_codg; //医疗目录编码
                int page_num = fsiRequest.Input.data.page_num; //当前页
                int page_size = fsiRequest.Input.data.page_size; //本页数据

                if (string.IsNullOrEmpty(fixmedins_code) || page_num == 0 || page_size == 0)
                {
                    throw new Exception("参数校验失败：传入的机构代码|当前页码|当前页条数为空，请检查入参！");
                }

                string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";

                if (string.IsNullOrEmpty(medins_list_codg))
                {
                    medins_list_codg = "%";
                }

                if (string.IsNullOrEmpty(medins_list_name))
                {
                    medins_list_name = "%";
                }

                if (string.IsNullOrEmpty(med_list_codg))
                {
                    med_list_codg = "%";
                }

                int now_page = (page_num - 1) * page_size;
                Random rnd = new Random();
                DateTime dateTime = DateTime.Now;
                string query = "select * from fsi_catalogcompup where fixmedins_code=@fixmedins_code and fixmedins_hilist_id like @medins_list_codg and fixmedins_hilist_name like @medins_list_name and med_list_codg like @med_list_codg limit @nowpage,@pagesize";
                string countQuery = "select count(*) from fsi_catalogcompup where fixmedins_code=@fixmedins_code and fixmedins_hilist_id like @medins_list_codg and fixmedins_hilist_name like @medins_list_name and med_list_codg like @med_list_codg";
                
                var parameter = new List<MySqlParameter>
                {
                    new MySqlParameter("@fixmedins_code", fixmedins_code),
                    new MySqlParameter("@medins_list_codg", "%" + medins_list_codg + "%"),
                    new MySqlParameter("@medins_list_name", "%" + medins_list_name + "%"),
                    new MySqlParameter("@med_list_codg", "%" + med_list_codg + "%"),
                    new MySqlParameter("@nowpage", now_page),
                    new MySqlParameter("@pagesize", page_size),
                };
                
                DataTable dt = await MySqlHelper.ExecuteQueryAsync(connectionString, query, parameter);
                int count = await MySqlHelper.ExecuteScalarAsync<int>(connectionString, countQuery, parameter);
                
                List<OutputDataQueryMedListMapByPage> dataList = new List<OutputDataQueryMedListMapByPage>();
                foreach (DataRow row in dt.Rows)
                {
                    OutputDataQueryMedListMapByPage item = new OutputDataQueryMedListMapByPage
                    {
                        fixmedins_code = row["fixmedins_code"].ToString(),
                        medins_list_codg = row["fixmedins_hilist_id"].ToString(),
                        medins_list_name = row["fixmedins_hilist_name"].ToString(),
                        insu_admdvs = fsiRequest.mdtrtarea_admvs.ToString(),
                        list_type = row["list_type"].ToString(),
                        med_list_codg = row["med_list_codg"].ToString(),
                        begndate = row["begndate"].ToString(),
                        enddate = row["enddate"].ToString(),
                        aprvno = row["aprvno"].ToString(),
                        dosform = row["dosform"].ToString(),
                        exct_cont = row["exct_cont"].ToString(),
                        item_cont = row["item_cont"].ToString(),
                        prcunt = row["prcunt"].ToString(),
                        spec = row["spec"].ToString(),
                        pacspec = row["pacspec"].ToString(),
                        memo = row["memo"].ToString(),
                        updt_time = row["create_time"].ToString(),
                        crte_time = row["create_time"].ToString()
                    };
                    dataList.Add(item);
                }

                var pageCount = (int)Math.Ceiling((double)count / (double)page_size);

                var output = new OutputQueryMedListMapByPage
                {
                    recordCounts = count,
                    pages = pageCount,
                    data = dataList,
                    size = page_size,
                    firstPage = page_num == 1,
                    lastPage = page_num >= pageCount,
                    startRow = 1,
                    endRow = dt.Rows.Count,
                    pageSize = page_size,
                    pageNum = page_num
                };
                return output;
            }
            catch (Exception ex)
            {
                LogHelper.Error(
                    $"[CatalogQueryService.QueryMedListMapByPage]定点医药机构目录上传记录数据获取异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
                throw;
            }
        }
        
        /// <summary>
        /// 1318 医保目录限价信息查询
        /// </summary>
        /// <param name="fsiRequest">入参</param>
        /// <param name="ControllerName">来源控制器名称</param>
        /// <returns>出参</returns>
        /// <exception cref="Exception"></exception>
        public async Task<OutputQueryLmtprcByPage> QueryLmtprcByPage(FsiRequestHeader<InputQueryLmtprcByPage> fsiRequest, string ControllerName)
        {
            try
            {
                string fsiadmvs = fsiRequest.mdtrtarea_admvs.Substring(0, 2);
                
                string query_date = fsiRequest.Input.data.query_date;//查询时间点
                string hilist_code = fsiRequest.Input.data.hilist_code;//医保目录编码
                string hilist_lmtpric_type = fsiRequest.Input.data.hilist_lmtpric_type; //医保目录限价类型
                string overlmt_dspo_way = fsiRequest.Input.data.overlmt_dspo_way;//医保目录超限处理方式
                
                string insu_admdvs = fsiRequest.Input.data.insu_admdvs;//参保机构医保区划
                string begndate = fsiRequest.Input.data.begndate;//开始日期
                string enddate = fsiRequest.Input.data.enddate;//结束日期
                string vali_flag = fsiRequest.Input.data.vali_flag;//有效标志
                string rid  = fsiRequest.Input.data.rid;// 唯一记录号
                string tabname = fsiRequest.Input.data.tabname;//表名
                string poolarea_no= fsiRequest.Input.data.poolarea_no;//统筹区编码
                string updt_time = fsiRequest.Input.data.updt_time;//更新时间
                
                int page_num = fsiRequest.Input.data.page_num; //当前页
                int page_size = fsiRequest.Input.data.page_size; //本页数据

                if (string.IsNullOrEmpty(updt_time) || page_num == 0 || page_size == 0)
                {
                    throw new Exception("参数校验失败：传入的结束时间|当前页码|当前页条数为空，请检查入参！");
                }

                string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
                


                int now_page = (page_num - 1) * page_size;
                Random rnd = new Random();
                DateTime dateTime = DateTime.Now;
                string query = "select  HILIST_CODE,HILIST_LMTPRIC_TYPE,OVERLMT_DSPO_WAY,INSU_ADMDVS,BEGNDATE,ENDDATE,HILIST_PRIC_UPLMT_AMT,VALI_FLAG,RID,UPDT_TIME,CRTER_ID,CRTER_NAME,CRTE_TIME,CRTE_OPTINS_NO,OPTER_ID,OPTER_NAME,OPT_TIME,OPTINS_NO,TABNAME,POOLAREA_NO from  ml_ybmlxj  where 1=1  ";
                string countQuery = "select count(*)  from ml_ybmlxj where 1=1 ";
                
                if (!string.IsNullOrEmpty(hilist_code))
                {
                    query += " and HILIST_CODE=@hilist_code ";
                    countQuery += " and HILIST_CODE=@hilist_code ";
                }

                if (!string.IsNullOrEmpty(hilist_lmtpric_type))
                {
                    query += " and HILIST_LMTPRIC_TYPE=@hilist_lmtpric_type ";
                    countQuery += " and HILIST_LMTPRIC_TYPE=@hilist_lmtpric_type ";
                }
                
                if (!string.IsNullOrEmpty(overlmt_dspo_way))
                {
                    query += " and OVERLMT_DSPO_WAY=@overlmt_dspo_way ";
                    countQuery += " and OVERLMT_DSPO_WAY=@overlmt_dspo_way ";
                }
                
                if (!string.IsNullOrEmpty(insu_admdvs))
                {
                    query += " and INSU_ADMDVS=@insu_admdvs ";
                    countQuery += " and INSU_ADMDVS=@insu_admdvs ";
                }
                
                if (!string.IsNullOrEmpty(vali_flag))
                {
                    query += " and VALI_FLAG=@vali_flag ";
                    countQuery += " and VALI_FLAG=@vali_flag ";
                }
                
                if (!string.IsNullOrEmpty(begndate))
                {
                    query += " and BEGNDATE >= @begndate ";
                    countQuery += " and BEGNDATE >= @begndate ";
                }
                
                if (!string.IsNullOrEmpty(enddate))
                {
                    query += " and ENDDATE <= @enddate ";
                    countQuery += " and ENDDATE <= @enddate ";
                }

                if (!string.IsNullOrEmpty(rid))
                {
                    query += " and RID=@rid ";
                    countQuery += " and RID=@rid ";
                }
                
                if (!string.IsNullOrEmpty(tabname))
                {
                    query += " and TABNAME=@tabname ";
                    countQuery += " and TABNAME=@tabname ";
                }
                
                if (!string.IsNullOrEmpty(poolarea_no))
                {
                    query += " and POOLAREA_NO=@poolarea_no ";
                    countQuery += " and POOLAREA_NO=@poolarea_no ";
                }
                
                if (!string.IsNullOrEmpty(updt_time))
                {
                    query += " and UPDT_TIME >= @updt_time ";
                    countQuery += " and UPDT_TIME >= @updt_time ";
                }
                
                query += " limit @nowpage,@pagesize";
                
                var parameter = new List<MySqlParameter>
                {
                    new MySqlParameter("@hilist_code", hilist_code),
                    new MySqlParameter("@hilist_lmtpric_type", hilist_lmtpric_type),
                    new MySqlParameter("@overlmt_dspo_way", overlmt_dspo_way),
                    new MySqlParameter("@rid", rid),
                    new MySqlParameter("@tabname", tabname),
                    new MySqlParameter("@poolarea_no", poolarea_no),
                    new MySqlParameter("@enddate", enddate),
                    new MySqlParameter("@vali_flag", vali_flag),
                    new MySqlParameter("@insu_admdvs", insu_admdvs),
                    new MySqlParameter("@begndate", begndate),
                    new MySqlParameter("@updt_time", updt_time),
                    new MySqlParameter("@nowpage", now_page),
                    new MySqlParameter("@pagesize", page_size),
                };
                
                DataTable dt = await MySqlHelper.ExecuteQueryAsync(connectionString, query, parameter);
                int count = await MySqlHelper.ExecuteScalarAsync<int>(connectionString, countQuery, parameter);
                
                List<OutputDataQueryLmtprcByPage> dataList = new List<OutputDataQueryLmtprcByPage>();
                foreach (DataRow row in dt.Rows)
                {
                    OutputDataQueryLmtprcByPage item = new OutputDataQueryLmtprcByPage
                    {
                        hilist_code = row["HILIST_CODE"].ToString(),
                        insu_admdvs = row["INSU_ADMDVS"].ToString(),
                        begndate = row["BEGNDATE"].ToString(),
                        enddate = row["ENDDATE"].ToString(),
                        vali_flag = row["VALI_FLAG"].ToString(),
                        
                        rid = row["RID"].ToString(),
                        updt_time = row["UPDT_TIME"].ToString(),
                        crter_id = row["CRTER_ID"].ToString(),
                        crter_name = row["CRTER_NAME"].ToString(),
                        crte_time = row["CRTE_TIME"].ToString(),
                        
                        crte_optins_no = row["CRTE_OPTINS_NO"].ToString(),
                        opter_id = row["OPTER_ID"].ToString(),
                        opter_name = row["OPTER_NAME"].ToString(),
                        opt_time = row["OPT_TIME"].ToString(),
                        optins_no = row["OPTINS_NO"].ToString(),
                        
                        poolarea_no = row["POOLAREA_NO"].ToString(),
                        hilist_lmtpric_type = row["HILIST_LMTPRIC_TYPE"].ToString(),
                        overlmt_dspo_way = row["OVERLMT_DSPO_WAY"].ToString(),
                        hilist_pric_uplmt_amt = Convert.ToDecimal(row["HILIST_PRIC_UPLMT_AMT"]),
                        tabname = row["TABNAME"].ToString(),
                         
                    };
                    dataList.Add(item);
                }

                var pageCount = (int)Math.Ceiling((double)count / (double)page_size);

                var output = new OutputQueryLmtprcByPage
                {
                    recordCounts = count,
                    pages = pageCount,
                    data = dataList,
                    size = page_size,
                    firstPage = page_num == 1,
                    lastPage = page_num >= pageCount,
                    startRow = 1,
                    endRow = dt.Rows.Count,
                    pageSize = page_size,
                    pageNum = page_num
                };
                return output;
            }
            catch (Exception ex)
            {
                LogHelper.Error(
                    $"[CatalogQueryService.QueryLmtprcByPage]医保目录限价信息数据获取异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
                throw;
            }
        }
        
        /// <summary>
        /// 1319 医保目录先自付比例信息查询
        /// </summary>
        /// <param name="fsiRequest">入参</param>
        /// <param name="ControllerName">来源控制器名称</param>
        /// <returns>出参</returns>
        /// <exception cref="Exception"></exception>
        public async Task<OutputQuerySelfpayByPage> QuerySelfpayByPage(FsiRequestHeader<InputQuerySelfpayByPage> fsiRequest, string ControllerName)
        {
            try
            {
                string fsiadmvs = fsiRequest.mdtrtarea_admvs.Substring(0, 2);
                
                string query_date = fsiRequest.Input.data.query_date;//查询时间点
                string hilist_code = fsiRequest.Input.data.hilist_code;//医保目录编码
                
                string selfpay_prop_psn_type = fsiRequest.Input.data.selfpay_prop_psn_type; //医保目录自付比例人员类别
                string selfpay_prop_type = fsiRequest.Input.data.selfpay_prop_type;//目录自付比例类别
                
                string insu_admdvs = fsiRequest.Input.data.insu_admdvs;//参保机构医保区划
                string begndate = fsiRequest.Input.data.begndate;//开始日期
                string enddate = fsiRequest.Input.data.enddate;//结束日期
                string vali_flag = fsiRequest.Input.data.vali_flag;//有效标志
                string rid  = fsiRequest.Input.data.rid;// 唯一记录号
                string tabname = fsiRequest.Input.data.tabname;//表名
                string poolarea_no= fsiRequest.Input.data.poolarea_no;//统筹区编码
                string updt_time = fsiRequest.Input.data.updt_time;//更新时间
                
                int page_num = fsiRequest.Input.data.page_num; //当前页
                int page_size = fsiRequest.Input.data.page_size; //本页数据

                if (string.IsNullOrEmpty(updt_time) || page_num == 0 || page_size == 0)
                {
                    throw new Exception("参数校验失败：传入的结束时间|当前页码|当前页条数为空，请检查入参！");
                }

                string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
                


                int now_page = (page_num - 1) * page_size;
                Random rnd = new Random();
                DateTime dateTime = DateTime.Now;
                string query = "select  HILIST_CODE,SELFPAY_PROP_PSN_TYPE,SELFPAY_PROP_TYPE,INSU_ADMDVS,BEGNDATE,ENDDATE,SELFPAY_PROP,VALI_FLAG,RID,UPDT_TIME,CRTER_ID,CRTER_NAME,CRTE_TIME,CRTE_OPTINS_NO,OPTER_ID,OPTER_NAME,OPT_TIME,OPTINS_NO,TABNAME,POOLAREA_NO from  ml_ybmlzfbl  where 1=1  ";
                string countQuery = "select count(*)  from ml_ybmlzfbl where 1=1 ";
                
                if (!string.IsNullOrEmpty(hilist_code))
                {
                    query += " and HILIST_CODE=@hilist_code ";
                    countQuery += " and HILIST_CODE=@hilist_code ";
                }

                if (!string.IsNullOrEmpty(selfpay_prop_psn_type))
                {
                    query += " and SELFPAY_PROP_PSN_TYPE=@selfpay_prop_psn_type ";
                    countQuery += " and SELFPAY_PROP_PSN_TYPE=@selfpay_prop_psn_type ";
                }
                
                if (!string.IsNullOrEmpty(selfpay_prop_type))
                {
                    query += " and SELFPAY_PROP_TYPE=@selfpay_prop_type ";
                    countQuery += " and SELFPAY_PROP_TYPE=@selfpay_prop_type ";
                }
                
                if (!string.IsNullOrEmpty(insu_admdvs))
                {
                    query += " and INSU_ADMDVS=@insu_admdvs ";
                    countQuery += " and INSU_ADMDVS=@insu_admdvs ";
                }
                
                if (!string.IsNullOrEmpty(vali_flag))
                {
                    query += " and VALI_FLAG=@vali_flag ";
                    countQuery += " and VALI_FLAG=@vali_flag ";
                }
                
                if (!string.IsNullOrEmpty(begndate))
                {
                    query += " and BEGNDATE >= @begndate ";
                    countQuery += " and BEGNDATE >= @begndate ";
                }
                
                if (!string.IsNullOrEmpty(enddate))
                {
                    query += " and ENDDATE <= @enddate ";
                    countQuery += " and ENDDATE <= @enddate ";
                }

                if (!string.IsNullOrEmpty(rid))
                {
                    query += " and RID=@rid ";
                    countQuery += " and RID=@rid ";
                }
                
                if (!string.IsNullOrEmpty(tabname))
                {
                    query += " and TABNAME=@tabname ";
                    countQuery += " and TABNAME=@tabname ";
                }
                
                if (!string.IsNullOrEmpty(poolarea_no))
                {
                    query += " and POOLAREA_NO=@poolarea_no ";
                    countQuery += " and POOLAREA_NO=@poolarea_no ";
                }
                
                if (!string.IsNullOrEmpty(updt_time))
                {
                    query += " and UPDT_TIME >= @updt_time ";
                    countQuery += " and UPDT_TIME >= @updt_time ";
                }
                
                query += " limit @nowpage,@pagesize";
                
                var parameter = new List<MySqlParameter>
                {
                    new MySqlParameter("@hilist_code", hilist_code),
                    new MySqlParameter("@selfpay_prop_psn_type", selfpay_prop_psn_type),
                    new MySqlParameter("@selfpay_prop_type", selfpay_prop_type),
                    new MySqlParameter("@rid", rid),
                    new MySqlParameter("@tabname", tabname),
                    new MySqlParameter("@poolarea_no", poolarea_no),
                    new MySqlParameter("@enddate", enddate),
                    new MySqlParameter("@vali_flag", vali_flag),
                    new MySqlParameter("@insu_admdvs", insu_admdvs),
                    new MySqlParameter("@begndate", begndate),
                    new MySqlParameter("@updt_time", updt_time),
                    new MySqlParameter("@nowpage", now_page),
                    new MySqlParameter("@pagesize", page_size),
                };
                
                DataTable dt = await MySqlHelper.ExecuteQueryAsync(connectionString, query, parameter);
                int count = await MySqlHelper.ExecuteScalarAsync<int>(connectionString, countQuery, parameter);
                
                List<OutputDataQuerySelfpayByPage> dataList = new List<OutputDataQuerySelfpayByPage>();
                foreach (DataRow row in dt.Rows)
                {
                    OutputDataQuerySelfpayByPage item = new OutputDataQuerySelfpayByPage
                    {
                        hilist_code = row["HILIST_CODE"].ToString(),
                        insu_admdvs = row["INSU_ADMDVS"].ToString(),
                        begndate = row["BEGNDATE"].ToString(),
                        enddate = row["ENDDATE"].ToString(),
                        vali_flag = row["VALI_FLAG"].ToString(),
                        
                        rid = row["RID"].ToString(),
                        updt_time = row["UPDT_TIME"].ToString(),
                        crter_id = row["CRTER_ID"].ToString(),
                        crter_name = row["CRTER_NAME"].ToString(),
                        crte_time = row["CRTE_TIME"].ToString(),
                        
                        crte_optins_no = row["CRTE_OPTINS_NO"].ToString(),
                        opter_id = row["OPTER_ID"].ToString(),
                        opter_name = row["OPTER_NAME"].ToString(),
                        opt_time = row["OPT_TIME"].ToString(),
                        optins_no = row["OPTINS_NO"].ToString(),
                        
                        poolarea_no = row["POOLAREA_NO"].ToString(),
                        tabname = row["TABNAME"].ToString(),
                        selfpay_prop = row["SELFPAY_PROP"].ToString() == "" ? 1 : Convert.ToDecimal(row["SELFPAY_PROP"]),
                        selfpay_prop_psn_type = row["SELFPAY_PROP_PSN_TYPE"].ToString(),
                        selfpay_prop_type = row["SELFPAY_PROP_TYPE"].ToString(),
                        
                    };
                    dataList.Add(item);
                }

                var pageCount = (int)Math.Ceiling((double)count / (double)page_size);

                var output = new OutputQuerySelfpayByPage
                {
                    recordCounts = count,
                    pages = pageCount,
                    data = dataList,
                    size = page_size,
                    firstPage = page_num == 1,
                    lastPage = page_num >= pageCount,
                    startRow = 1,
                    endRow = dt.Rows.Count,
                    pageSize = page_size,
                    pageNum = page_num
                };
                return output;
            }
            catch (Exception ex)
            {
                LogHelper.Error(
                    $"[CatalogQueryService.QuerySelfpayByPage]医保目录先自付比例信息获取异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
                throw;
            }
        }
        
        /// <summary>
        /// 1901 字典查询
        /// </summary>
        /// <param name="fsiRequest">入参</param>
        /// <param name="ControllerName">来源控制器名称</param>
        /// <returns>出参</returns>
        /// <exception cref="Exception"></exception>
        public async Task<OutputQueryDataDic> QueryDataDic(FsiRequestHeader<InputQueryDataDic> fsiRequest, string ControllerName)
        {
            try
            {
                int page_num = fsiRequest.Input.data.page_num;
                int page_size = fsiRequest.Input.data.page_size;
                string vali_flag = fsiRequest.Input.data.vali_flag;
                string date = fsiRequest.Input.data.date;
                string type = fsiRequest.Input.data.type;
                string parent_value = fsiRequest.Input.data.parent_value;

                if (page_num <= 0 || page_size <= 0)
                {
                    throw new Exception($"参数校验失败：传入的分页参数异常！page_num【{page_num}】 page_size【{page_size}】 不能小于等于0！请检查入参！");
                }

                int maxpage = (int)((page_num - 1) * page_size);

                List<OutputQueryDataDicList> dicList = new List<OutputQueryDataDicList>();
                string connectionString =
                    $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
                string query = "select  * From  medicare_dist where 1=1 ";
                var parameters = new List<MySqlParameter>();

                if (!string.IsNullOrEmpty(vali_flag))
                {
                    query += " and VALI_FLAG=@vali_flag";
                    parameters.Add(new MySqlParameter("@vali_flag", vali_flag));
                }

                if (!string.IsNullOrEmpty(date))
                {
                    query += " and OPT_TIME>=@OPT_TIME";
                    parameters.Add(new MySqlParameter("@OPT_TIME", date));
                }

                if (!string.IsNullOrEmpty(type))
                {
                    query += " and DIC_TYPE_CODE=@DIC_TYPE_CODE";
                    parameters.Add(new MySqlParameter("@DIC_TYPE_CODE", type));
                }

                if (!string.IsNullOrEmpty(parent_value))
                {
                    query += " and PRNT_DIC_VAL_CODE=@PRNT_DIC_VAL_CODE";
                    parameters.Add(new MySqlParameter("@PRNT_DIC_VAL_CODE", parent_value));
                }

                query += " order by DIC_TYPE_CODE,SEQ limit @maxpage,@pagesize";
                parameters.Add(new MySqlParameter("@pagesize", page_size));
                parameters.Add(new MySqlParameter("@maxpage", maxpage));
                DataTable dt = await MySqlHelper.ExecuteQueryAsync(connectionString, query, parameters);

                foreach (DataRow row in dt.Rows)
                {
                    var dicinfo = new OutputQueryDataDicList()
                    {
                        type = row["DIC_TYPE_CODE"].ToString(),
                        label = row["NAT_DIC_VAL_NAME"].ToString(),
                        value = row["NAT_DIC_VAL_CODE"].ToString(),
                        parent_value = row["PRNT_DIC_VAL_CODE"].ToString(),
                        sort = Convert.IsDBNull(dt.Rows[0]["SEQ"]) ? 0 : int.Parse(dt.Rows[0]["SEQ"].ToString()),
                        vali_flag = row["VALI_FLAG"].ToString(),
                        create_user = row["CRTER_NAME"].ToString(),
                        create_date = row["CRTE_TIME"].ToString(),
                        version = row["VER"].ToString()
                    };
                    dicList.Add(dicinfo);
                }

                return new OutputQueryDataDic()
                {
                    list = dicList
                };
            }
            catch (Exception ex)
            {
                LogHelper.Error(
                    $"[CatalogQueryService.QueryPsnPriorityInfo]字典数据获取异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
                throw;
            }
        }
    }
}
